package cs5226.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import cs5226.model.*;

public class SharePoolDAO extends AWRStatisticsDAO{	
		
	
	
	public static List<SingleValueStatistics> searchLibraryCacheStatistics() throws SQLException {
		String exprStat = "SUM(a.pinhits) * 1.0 / SUM(a.pins)";
		String awrTable = "DBA_HIST_LIBRARYCACHE";
		
		
		return searchAWRStatistics(SingleValueStatistics.class, exprStat, awrTable, DAYSLATEST);
	}
	
	public static List<SingleValueStatistics> searchLibraryCacheStatistics  (String fromDate, String toDate) throws SQLException {
		String exprStat = "SUM(a.pinhits) * 1.0 / SUM(a.pins)";
		String awrTable = "DBA_HIST_LIBRARYCACHE";
		String filter = null;
		
		return searchAWRStatisticsWithinAPeriod(fromDate, toDate, SingleValueStatistics.class, exprStat, awrTable, DAYSLATEST, filter);
	}
	
	public static List<SingleValueStatistics> searchLibraryCacheStatisticsLatest() throws SQLException {
		String exprStat = "SUM(a.pinhits) * 1.0 / SUM(a.pins)";
		String awrTable = "DBA_HIST_LIBRARYCACHE";
		String filter = null;
		
		return searchAWRStatisticsLatest(SingleValueStatistics.class, exprStat, awrTable, DAYSLATEST, filter);
	}

	public static List<SingleValueStatistics> searchDictionaryCacheStatistics()
			throws SQLException {
		String exprStat = "(SUM(a.GETS - a.GETMISSES) / SUM(a.GETS))";
		String awrTable = "DBA_HIST_ROWCACHE_SUMMARY";
		
		return searchAWRStatistics(SingleValueStatistics.class, exprStat, awrTable, DAYSLATEST);
	}
	
	public static List<SingleValueStatistics> searchDictionaryCacheStatistics(String fromDate, String toDate)
			throws SQLException {
		String exprStat = "(SUM(a.GETS - a.GETMISSES) / SUM(a.GETS))";
		String awrTable = "DBA_HIST_ROWCACHE_SUMMARY";
		String filter = null;
		
		return searchAWRStatisticsWithinAPeriod(fromDate, toDate, SingleValueStatistics.class, exprStat, awrTable, DAYSLATEST, filter);
	}
	
	public static List<SingleValueStatistics> searchDictionaryCacheStatisticsLatest() throws SQLException {
		String exprStat = "(SUM(a.GETS - a.GETMISSES) / SUM(a.GETS))";
		String awrTable = "DBA_HIST_ROWCACHE_SUMMARY";
		String filter = null;
		
		return searchAWRStatisticsLatest(SingleValueStatistics.class, exprStat, awrTable, DAYSLATEST, filter);
	}
		
	public static List<PercentageSingleValueStatistics> searchSharedPoolFreeStatistics()
			throws SQLException {
		String exprStat = "(100 * sum(decode(name,'free memory',bytes))/sum(bytes))";
		String awrTable = "DBA_HIST_SGASTAT";
		String filter = "a.POOL = 'shared pool'";
				
		return searchAWRStatistics(PercentageSingleValueStatistics.class, exprStat, awrTable, DAYSLATEST, filter);
	}

	public static List<PercentageSingleValueStatistics> searchSharedPoolFreeStatistics(String fromDate, String toDate)
			throws SQLException {
		String exprStat = "(100 * sum(decode(name,'free memory',bytes))/sum(bytes))";
		String awrTable = "DBA_HIST_SGASTAT";
		String filter = "a.POOL = 'shared pool'";
		
		return searchAWRStatisticsWithinAPeriod(fromDate, toDate, PercentageSingleValueStatistics.class, exprStat, awrTable, DAYSLATEST, filter);
	}
	
	public static List<PercentageSingleValueStatistics> searchSharedPoolFreeStatisticsLatest() throws SQLException {
		String exprStat = "(100 * sum(decode(name,'free memory',bytes))/sum(bytes))";
		String awrTable = "DBA_HIST_SGASTAT";
		String filter = "a.POOL = 'shared pool'";
		
		return searchAWRStatisticsLatest(PercentageSingleValueStatistics.class, exprStat, awrTable, DAYSLATEST, filter);
	}
}
